# 针对DIM层建表开发操作、建模

# 一、创建itcast_clazz报名课程表
CREATE EXTERNAL TABLE IF NOT EXISTS itcast_dimen.itcast_clazz (
    id                  INT COMMENT 'ems课程id(非自增)',
    create_date_time    STRING COMMENT '创建时间',
    update_date_time    STRING COMMENT '最后更新时间',
    deleted             STRING COMMENT '是否被删除（禁用）',
    itcast_school_id    STRING COMMENT 'ems校区ID',
    itcast_school_name  STRING COMMENT 'ems校区名称',
    itcast_subject_id   STRING COMMENT 'ems学科ID',
    itcast_subject_name STRING COMMENT 'ems学科名称',
    itcast_brand        STRING COMMENT 'ems品牌',
    clazz_type_state    STRING COMMENT '班级类型状态',
    clazz_type_name     STRING COMMENT '班级类型名称',
    teaching_mode       STRING COMMENT '授课模式',
    start_time          STRING COMMENT '开班时间',
    end_time            STRING COMMENT '毕业时间',
    comment             STRING COMMENT '备注',
    detail              STRING COMMENT '详情(比如：27期)',
    uncertain           STRING COMMENT '待定班(0:否,1:是)',
    tenant              INT COMMENT '租户',
    ends_time           STRING COMMENT '有效时间'
) COMMENT '班级信息表'
PARTITIONED BY (starts_time STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS ORC
TBLPROPERTIES ('orc.compress' = 'SNAPPY','orc.create.index' = 'true','orc.bloom.filter.columns' = 'id');

# 二、Sqoop导入数据到Hive表中
sqoop import \
--connect jdbc:mysql://192.168.52.150:3306/scrm \
--username root \
--password 123456 \
--query 'select *,'9999-12-31' as ends_time,date_sub(current_date, interval 1 day) as starts_time from itcast_clazz where $CONDITIONS' \
--hcatalog-database itcast_dimen \
--hcatalog-table itcast_clazz \
-m 4 \
--split-by id
